from math import sin,cos,pi,radians
from openpyxl import load_workbook
from time import time
import os,datetime


def getPoint(lon1,lat1,azimuth,r):      #根据方位角和距离计算经纬度
    ARC = 6371393
    lat1_rad = radians(lat1)
    # lon1_rad = radians(lon1)
    alpha_rad = radians(azimuth)

    lon2 = lon1 + r*sin(alpha_rad)/(ARC*cos(lat1_rad)*2*pi/360)
    lat2 = lat1 + r*cos(alpha_rad)/(ARC*2*pi/360)
    return lon2,lat2

def getRadius(freq,covType):    #根据频点和区域类型计算图形半径
    if freq==1:
        if covType =="市区":
            r = 1*25
        elif covType == "郊区":
            r = 1*50
        elif covType == "农村":
            r = 1*100
        else:
            r = 1*75
    elif freq==3:
        if covType =="市区":
            r = 3*25
        elif covType == "郊区":
            r = 3*50
        elif covType == "农村":
            r = 3*100
        else:
            r = 3*75
    elif freq==5:
        if covType =="市区":
            r = 5*25
        elif covType == "郊区":
            r = 5*50
        elif covType == "农村":
            r = 5*100
        else:
            r = 5*75
    elif freq == 8:
        if covType == "市区":
            r = 5*25
        elif covType == "郊区":
            r = 5*50
        elif covType == "农村":
            r = 5*100
        else:
            r = 5*75
    else:
        r = 300
    return r

t0 = time()
print("读取Cells.xlsx表格............")
WorkBook = load_workbook("cells.xlsx")
t1 = time()
print("读取完成，耗时：",str(t1-t0),"秒")

sheet1 = WorkBook.active
maxRows = sheet1.max_row
maxCols = sheet1.max_column
col_name = 0        #小区名的列号
col_lon  = 0        #经度的列号
col_lat  = 0        #纬度的列号
col_azimuth = 0     #方位角的列号
col_flagInOut = 0   #室内室外标志列的列号
col_covType = 0     #覆盖区域类型列的列号
col_freq = 0        #频段列的列号
col_pci = 0         #PCI列的列号 
# print(col_name,col_lon,col_lat,col_azimuth,col_flagInOut,col_covType,col_freq,col_pci)

for col in range(1,maxCols+1):
    # print(sheet1.cell(1,col).value)
    if sheet1.cell(1,col).value == "小区名":
        col_name = col
    elif sheet1.cell(1,col).value == "扇区经度":
        col_lon =col
    elif sheet1.cell(1,col).value == "扇区纬度":
        col_lat =col
    elif sheet1.cell(1,col).value == "天线方位角":
        col_azimuth = col
    elif sheet1.cell(1,col).value == "物理小区标识":
        col_pci = col
    elif sheet1.cell(1,col).value == "频段指示":
        col_freq = col
    elif sheet1.cell(1,col).value == "室内外标识":
        col_flagInOut = col
    elif sheet1.cell(1,col).value == "扇区覆盖区域类型":
        col_covType = col
# print(col_name,col_lon,col_lat,col_azimuth,col_flagInOut,col_covType,col_freq,col_pci)


head     = open("head.kml",mode='r',encoding='utf-8-sig')                          #KML头文件
tmpfile1 = open("tmpfile1.kml",mode='w+',encoding='utf-8-sig')                     #2.1G图层临时文件
tmpfile2 = open("tmpfile2.kml",mode='w+',encoding='utf-8-sig')                     #1.8G图层临时文件
tmpfile3 = open("tmpfile3.kml",mode='w+',encoding='utf-8-sig')                     #800M图层临时文件
cells    = open("cells.kml",mode='w',encoding='utf-8-sig')                         #合并输出KML文件

#########################################增加<Folder>信息####################################################################
tmp = "\t<Folder>"
tmp = tmp + "<name>"+"2.1G图层</name>\n"
tmp = tmp + "\t\t<Folder>"
tmp = tmp + "<name>小区信息</name>\n"
tmp = tmp + "\t\t<Style>\n" 
tmp = tmp + "\t\t<ListStyle>\n" 
tmp = tmp + "\t\t\t<listItemType>check</listItemType>\n" 
tmp = tmp + "\t\t\t<bgColor>00ffffff</bgColor>\n"
tmp = tmp + "\t\t\t<maxSnippetLines>1</maxSnippetLines>\n"
tmp = tmp + "\t\t</ListStyle>\n" 
tmp = tmp + "\t\t</Style>\n" 
tmpfile1.writelines(tmp)

tmp = "\t<Folder>"
tmp = tmp + "<name>"+"1.8G图层</name>\n"
tmp = tmp + "\t\t<Folder>"
tmp = tmp + "<name>小区信息</name>\n"
tmp = tmp + "\t\t<Style>\n" 
tmp = tmp + "\t\t<ListStyle>\n" 
tmp = tmp + "\t\t\t<listItemType>check</listItemType>\n" 
tmp = tmp + "\t\t\t<bgColor>00ffffff</bgColor>\n"
tmp = tmp + "\t\t\t<maxSnippetLines>1</maxSnippetLines>\n"
tmp = tmp + "\t\t</ListStyle>\n" 
tmp = tmp + "\t\t</Style>\n" 
tmpfile2.writelines(tmp)

tmp = "\t<Folder>"
tmp = tmp + "<name>"+"800M图层</name>\n"
tmp = tmp + "\t\t<Folder>"
tmp = tmp + "<name>小区信息</name>\n"
tmp = tmp + "\t\t<Style>\n" 
tmp = tmp + "\t\t<ListStyle>\n" 
tmp = tmp + "\t\t\t<listItemType>check</listItemType>\n" 
tmp = tmp + "\t\t\t<bgColor>00ffffff</bgColor>\n"
tmp = tmp + "\t\t\t<maxSnippetLines>1</maxSnippetLines>\n"
tmp = tmp + "\t\t</ListStyle>\n" 
tmp = tmp + "\t\t</Style>\n" 
tmpfile3.writelines(tmp)
#########################################第一个循环输出小区文本信息##########################################################
t0 = time()
for i in range(2,maxRows+1):
    name = sheet1.cell(i,col_name).value
    lon  = sheet1.cell(i,col_lon).value
    lat  = sheet1.cell(i,col_lat).value
    ang = str(sheet1.cell(i,col_azimuth).value)
    flagInOut = sheet1.cell(i,col_flagInOut).value
    zoneType  = sheet1.cell(i,col_covType).value
    freq      = sheet1.cell(i,col_freq).value

    r = getRadius(freq,zoneType)
    angs = ang.split('/')
       
    tmp = ""
    for azimuth in angs:
        lon_p,lat_p = getPoint(lon,lat,float(azimuth),r)
        tmp = tmp + "\t\t<Placemark>"
        tmp = tmp + "<name>" + name + "</name>\n"
        tmp = tmp + "\t\t<styleUrl>#msn_wht-blank</styleUrl>\n"
        tmp = tmp + "\t\t<Point>\n"        
        if flagInOut=="室内" or flagInOut == "室分":
            tmp = tmp + "\t\t\t<coordinates>" + str(lon) + "," + str(lat) + ",0 " + "</coordinates>\n"
        if flagInOut == "室外":
            tmp = tmp + "\t\t\t<coordinates>" + str(lon_p)+"," + str(lat_p) + ",0 " + "</coordinates>\n"
        tmp = tmp + "\t\t</Point>\n"
        tmp = tmp + "\t\t</Placemark>\n"
    
    if freq == 1 :
        tmpfile1.writelines(tmp)
    elif freq == 3:
        tmpfile2.writelines(tmp)
    elif freq == 5:
        tmpfile3.writelines(tmp)

    
    print("\r","处理第",i-1,"个小区信息",end='')
t1 = time()
print("\t耗时:",str(t1-t0),"秒")
#########################################结束小区信息</Folder>#################################################################
tmp = "\t\t</Folder>\n"
tmpfile1.writelines(tmp)
tmpfile2.writelines(tmp)
tmpfile3.writelines(tmp)


########################################增加小区图形<Folder>####################################################################
tmp = "\t\t<Folder>"
tmp = tmp + "<name>小区图形</name>\n"
tmpfile1.writelines(tmp)
tmpfile2.writelines(tmp)
tmpfile3.writelines(tmp)
########################################第二个循环输出小区扇形图形###########################################################
t0 = time()
for i in range(2,maxRows+1):
    name = sheet1.cell(i,col_name).value
    lon  = sheet1.cell(i,col_lon).value
    lat  = sheet1.cell(i,col_lat).value
    ang  = str(sheet1.cell(i,col_azimuth).value)
    flagInOut = sheet1.cell(i,col_flagInOut).value
    zoneType  = sheet1.cell(i,col_covType).value
    freq      = sheet1.cell(i,col_freq).value

    r = getRadius(freq,zoneType)
    angs = ang.split('/')
    tmp = ""
    for azimuth in angs:
        tmp = tmp + "\t\t<Placemark>"+"<name>"+name+"</name>\n"
        tmp = tmp + "\t\t<description>\n"+"\t\t\t<![CDATA[<table border=1 width=650>\n"
        col = 1
        for group in range(0,round(maxCols/4)+1):
            h1 = str(sheet1.cell(row=1,column=col).value)
            if h1 == "None":
                h1 = " "
            h2 = str(sheet1.cell(row=1,column=col+1).value)
            if h2 == "None":
                h2 = " "
            h3 = str(sheet1.cell(row=1,column=col+2).value)
            if h3 == "None":
                h3 = " "
            h4 = str(sheet1.cell(row=1,column=col+3).value)
            if h4 == "None":
                h4 = " "

            d1 = str(sheet1.cell(row=i,column=col).value)
            if d1 == "None":
                d1 = " "
            d2 = str(sheet1.cell(row=i,column=col+1).value)
            if d2 == "None":
                d2 = " "
            d3 = str(sheet1.cell(row=i,column=col+2).value)
            if d3 == "None":
                d3 = " "
            d4 = str(sheet1.cell(row=i,column=col+3).value)
            if d4 == "None":
                d4 = " "

            tmp = tmp + "\t\t\t<tr><th>"+h1+"</th><th>"+h2+"</th><th>"+h3+"</th><th>"+h4+"</th></tr>\n"
            tmp = tmp + "\t\t\t<tr><td>"+d1+"</td><td>"+d2+"</td><td>"+d3+"</td><td>"+d4+"</td></tr>\n"
            col = col+4

        tmp = tmp + "\t\t\t</table>]]>\n" + "\t\t</description>\n"

        pci = sheet1.cell(row=i,column=col_pci).value
        Mod3 =  pci%3
        tmp = tmp + "\t\t\t<styleUrl>#msn_ylw-pushpin" + str(Mod3) + "</styleUrl>\n"
        tmp = tmp + "\t\t<Polygon>"
        tmp = tmp +"<tessellate>0</tessellate>\n"
        tmp = tmp +"\t\t\t<outerBoundaryIs>\n"
        tmp = tmp +"\t\t\t<LinearRing>\n"
        tmp = tmp +"\t\t\t<coordinates>\n"
        if freq ==1:
            SectorAng = 30
        if freq ==3:
            SectorAng = 20
        if freq ==5:
            SectorAng = 10
        A1 = (int(azimuth)-SectorAng)%360   #第一点方位角
        A2 = int(azimuth )                  #中间点方位角
        A3 = (int(azimuth)+SectorAng)%360   #第三点方位角

        if flagInOut == "室内" or flagInOut == "室分":
            tmp = tmp +"\t\t\t"
            for m in range(1,9):
                lon_p,lat_p = getPoint(lon,lat,m*45,20)
                tmp = tmp +str(lon_p)+","+str(lat_p)+",0 "
            tmp = tmp +"\n"
        elif flagInOut =="室外":
            tmp = tmp + "\t\t\t"
            tmp = tmp + str(lon)+","+str(lat)+",0 "  #原点
            lon_p,lat_p = getPoint(lon,lat,A1,r)
            tmp = tmp + str(lon_p)+","+str(lat_p)+",0 " #第一点
            lon_p,lat_p = getPoint(lon,lat,A2,r)
            tmp = tmp + str(lon_p)+","+str(lat_p)+",0 " #第二点
            lon_p,lat_p = getPoint(lon,lat,A3,r)
            tmp = tmp + str(lon_p)+","+str(lat_p)+",0 " #第三点
            tmp = tmp + str(lon)+","+str(lat)+",0 \n"  #原点
        tmp = tmp +"\t\t\t</coordinates>\n"
        tmp = tmp +"\t\t\t</LinearRing>\n"
        tmp = tmp +"\t\t\t</outerBoundaryIs>\n"
        tmp = tmp +"\t\t</Polygon>\n"

        tmp = tmp + "\t\t</Placemark>\n" 

    if freq == 1 :
        tmpfile1.writelines(tmp)
    elif freq == 3:
        tmpfile2.writelines(tmp)
    elif freq == 5:
        tmpfile3.writelines(tmp)

    print("\r","处理第",i-1,"个小区图形",end='')
t1 = time()
print("\t耗时:",str(t1-t0),"秒")

########################################结束小区图形<Folder>####################################################################
tmp = "\t\t</Folder>\n"
tmpfile1.writelines(tmp)
tmpfile2.writelines(tmp)
tmpfile3.writelines(tmp)

########################################结束图层<Folder>####################################################################
tmp = "\t</Folder>\n"
tmpfile1.writelines(tmp)
tmpfile2.writelines(tmp)
tmpfile3.writelines(tmp)

########################################合并头文件、中间图层文件、文件尾#################################################
tmp = head.readlines()
head.close()
d = datetime.date.today()
tmp[3]="\t<name>"+str(d)+"</name>\n"

cells.writelines(tmp)

tmpfile1.seek(0)
tmp = tmpfile1.readlines()
tmpfile1.close()
cells.writelines(tmp)

tmpfile2.seek(0)
tmp = tmpfile2.readlines()
tmpfile2.close()
cells.writelines(tmp)

tmpfile3.seek(0)
tmp = tmpfile3.readlines()
tmpfile3.close()
cells.writelines(tmp)

tmp = "</Document>\n"+"</kml>\n"
cells.writelines(tmp)
cells.close()
print("输出完成！")

os.remove("tmpfile1.kml")
os.remove("tmpfile2.kml")
os.remove("tmpfile3.kml")